package com.dlm.service.impl;

import com.dlm.dao.Technician;
import com.dlm.service.ITechnicianService;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.DbKit;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by chongsong on 15/2/1.
 * <mailto>chong.song@autonavi.com</mailto>
 */
public class TechnicianServiceImpl implements ITechnicianService {
    @Override
    public boolean add(Technician technician) throws Exception {
        return technician.save();
    }

    @Override
    public boolean delete(long id) throws Exception {
        return Technician.dao.deleteById(id);
    }

    @Override
    public boolean update(Technician technician) throws Exception {
        return technician.update();
    }

    @Override
    public Technician findById(long id) throws Exception {
        String select = "select t.id,t.name,t.comment,t.six,t.avg_price,t.head_img,t.is_auth,t.order_num,t.work_end,t.work_start,e.pd,e.cd,e.td,e.ada,e.adb,e.adc ";
        String from = "from technician t left join evaluate e on e.tech_id = t.id where t.id = " + id;
        System.out.println(select + from);
        List<Technician> list = Technician.dao.find(select + from);
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public Page<Technician> findByPage(long itemId, double x, double y, int pn, int ps) throws Exception {
        if(itemId <= 0){
            String center = "GeomFromText('POINT(" + x + " " + y + ")')";
            int radius = 10;
            String select = "SELECT id,avg_price,order_num,name,comment,city,address,six,create_time,head_img,SQRT(POW(ABS(X(pos)-X(" + center + ")),2)+POW(ABS(Y(pos)-Y(" + center + ")),2)) AS distance";
            String from = "FROM technician WHERE SQRT(POW(ABS(X(pos)-X(" + center + ")),2)+POW(ABS(Y(pos)-Y(" + center + ")),2))<" + radius + " order by distance";
            System.out.println(select);
            System.out.println(from);
            Object[] param = {};
            return Technician.dao.paginate(pn, ps, select, from, param);
        }else{
            String center = "GeomFromText('POINT(" + x + " " + y + ")')";
            int radius = 10;
            String select = "SELECT id,avg_price,order_num,name,comment,city,address,six,create_time,head_img,tem.t_price,tem.use_pro,tem.spend_time,tem.old_price,SQRT(POW(ABS(X(pos)-X(" + center + ")),2)+POW(ABS(Y(pos)-Y(" + center + ")),2)) AS distance";
            String from = "FROM technician t,( SELECT ti.tech_id,ti.price t_price, ti.use_pro use_pro,ti.spend_time,ti.old_price FROM t_item ti, r_item ri, item i WHERE i.id = ri.item_id " +
                    "AND ri.ti_id = ti.id AND i.id = 1 ) tem WHERE tem.tech_id = t.id and SQRT(POW(ABS(X(pos)-X(" + center + ")),2)+POW(ABS(Y(pos)-Y(" + center + ")),2))<" + radius + " order by distance";
            System.out.println(select);
            System.out.println(from);
            Object[] param = {};
            return Technician.dao.paginate(pn, ps, select, from, param);
        }
    }

    @Override
    public boolean updatePos(double x, double y, long id) {
        try {
            Connection conn = DbKit.getConfig().getConnection();
            if (conn != null) {
                String sql = "update technician t set t.pos = GeomFromText('Point(" + x + " " + y + ")') where id = ?;";
                System.out.println(sql);
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setObject(1, id);
                int result = ps.executeUpdate();
                return result >= 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public List<Technician> findWT(double x, double y, String date, String time) {
        String center = "GeomFromText('POINT(" + x + " " + y + ")')";
        int radius = 10;
        String sql = "SELECT t.*,SQRT(POW(ABS(X(pos)-X(" + center + ")),2)+POW(ABS(Y(pos)-Y(" + center + ")),2)) AS distance " +
                "FROM technician t WHERE t.id NOT IN (" +
                "SELECT o.tech_id FROM `order` o WHERE o.begin_date = '" + date + "' AND o.begin_time = '" + time + "') " +
                "AND t.id NOT IN (" +
                "SELECT tr.tech_id FROM tech_rest tr WHERE tr.rest_date = '" + date + "' AND tr.rest_time = '" + time + "')" +
                " and SQRT(POW(ABS(X(pos)-X(" + center + ")),2)+POW(ABS(Y(pos)-Y(" + center + ")),2)) < " + radius + " order by distance";
        System.out.println(sql);
        List<Technician> list = Technician.dao.find(sql);
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }


}
